import pandas as pd
import numpy as np
import pandas as pd
# Read the Excel file
df = pd.read_excel('Datasets/GasByCounty.xlsx')
# Reshape the DataFrame
# The melt function is used to transform the DataFrame from a wide format to a long format.
# It uses 'County' and 'Sector' as identifier variables and 'Timestamp' as the variable name for the melted column.
# The 'Yearly Data' column contains the values that are being melted.
df = df.melt(id_vars=['County', 'Sector'],
var_name='Timestamp', value_name='Yearly Data')
# Convert the "Timestamp" column to datetime format representing the year
# Commented out in this code snippet, but you can uncomment it if you want to convert the "Timestamp" column to datetime format.
# The format='%Y' specifies that the expected format of the values in the "Timestamp" column is year-only.
# By default, the pd.to_datetime function tries to infer the format, but specifying it explicitly can help avoid ambiguity.
# The resulting datetime values will represent the year extracted from the original values in the "Timestamp" column.
# Sort the DataFrame by County and Timestamp
# The sort_values function is used to sort the DataFrame by the specified columns.
# In this case, the DataFrame is sorted first by 'County' and then by 'Timestamp'.
df = df.sort_values(['County', 'Timestamp'])
# Print the resulting DataFrame
# The set_index function is used to set the "Timestamp" column as the new index of the DataFrame.
# The resulting DataFrame, df_indexd, will have the "Timestamp" column removed from the DataFrame and used as the index instead.
# This can be useful for indexing and accessing data based on the timestamp values.
df_indexd = df.set_index('Timestamp')
# The resulting DataFrame, df_indexd, will contain the original columns ('County' and 'Sector') along with the 'Yearly Data' column.
# The index will be the "Timestamp" column, which represents the year.
import pandas as pd
import plotly.graph_objects as go
from pmdarima import auto_arima
# Read the Excel file
df = pd.read_excel('Datasets/GasByCounty.xlsx')
# Reshape the DataFrame
# The melt function is used to transform the DataFrame from a wide format to a long format.
# It uses 'County' and 'Sector' as identifier variables and 'Timestamp' as the variable name for the melted column.
# The 'Yearly Data' column contains the values that are being melted.
df = df.melt(id_vars=['County', 'Sector'],
var_name='Timestamp', value_name='Yearly Data')
# Sort the DataFrame by County and Timestamp
# The sort_values function is used to sort the DataFrame by the specified columns.
# In this case, the DataFrame is sorted first by 'County' and then by 'Timestamp'.
df = df.sort_values(['County', 'Timestamp'])
# Drop rows with missing values (NaN)
# The dropna function is used to remove rows that contain missing values (NaN) from the DataFrame.
# This is done to ensure that only rows with complete data are used for analysis.
df = df.dropna()
# The resulting DataFrame, df, will contain the original columns ('County', 'Sector', 'Timestamp', 'Yearly Data'),
# with any rows containing missing values removed.
# Iterate over each country and sector
for country in df['County'].unique():
for sector in df['Sector'].unique():
fig = go.Figure()
# Get the energy consumption data for the current country and sector
df_filter = df[(df['County'] == country) & (
df['Sector'] == sector)][['Timestamp', 'Yearly Data']]
# Convert the "Timestamp" column to datetime format representing the year
try:
df_filter['Timestamp'] = pd.to_datetime(df_filter['Timestamp'], format='%Y')
except ValueError:
print(f"Skipping country: {country}, sector: {sector} due to invalid timestamp format")
continue
if df_filter.shape[0]>10:
df_filter_index = df_filter.set_index('Timestamp')
# Prepare the data for modeling
years = df_filter_index.index
energy_consumption = df_filter_index.values.flatten()
# Split the data into training and testing
# Use all data except the last 5 years for training
train_data = energy_consumption[:-5]
test_data = energy_consumption[-5:] # Use the last 5 years for testing
# Fit the auto ARIMA model
model = auto_arima(train_data, seasonal=True)
model.fit(train_data)
# Generate predictions
predictions = model.predict(n_periods=len(test_data))
# Plot the training data
fig.add_trace(go.Scatter(
x=years[:-5], y=train_data, mode='lines+markers', name='Training Data'))
# Plot the predictions
fig.add_trace(go.Scatter(
x=years[-5:], y=test_data, mode='lines+markers', name='Testing Data'))
fig.add_trace(go.Scatter(
x=years[-5:], y=predictions, mode='lines+markers', name='Predictions'))
# Update the layout
fig.update_layout(title=f'Gas Consumption Forecast Country : {country} : Sector {sector} ',
xaxis_title='Year', yaxis_title='Energy Consumption')
# Show the plot
fig.show()